import psycopg2
import config
from datetime import date, datetime
import decimal

class MyData:
    def __init__(self):
        self.con = psycopg2.connect(database=config.db_name, user=config.db_user, password=config.db_password, host=config.db_host, port=config.db_port)

    def add(self, title: str, amount: decimal.Decimal, gentime: datetime):
        sql = """INSERT INTO public."Accounts"(
	                "Title", "Amount", "GenTime", "CreateTime")
	                VALUES (%(Title)s, %(Amount)s, %(GenTime)s, %(CreateTime)s);"""
        data = {"Title": title, "Amount": amount, "GenTime": gentime, "CreateTime": datetime.now()}
        cur = self.con.cursor()
        cur.execute(sql, data)
        self.con.commit()        

    def get(self, id: int):
        sql = """SELECT "Id", "Title", "Amount", "GenTime", "CreateTime" FROM public."Accounts" WHERE "Id" = %(Id)s;"""
        cur = self.con.cursor()
        cur.execute(sql, {"Id": id})
        return cur.fetchone()

    def update(self, id: int, title: str, amount: decimal.Decimal, gentime: datetime):
        data = {"Id": id, "Title": title, "Amount": amount, "GenTime": gentime}
        sql = """UPDATE public."Accounts"
	                SET "Title"=%(Title)s, "Amount"=%(Amount)s, "GenTime"=%(GenTime)s
	            WHERE "Id"=%(Id)s;"""
        cur = self.con.cursor()
        cur.execute(sql, data)
        self.con.commit()     

    def delete(self, id: int):
        sql = """DELETE FROM public."Accounts" WHERE "Id"=%(id)s;"""
        cur = self.con.cursor()
        cur.execute(sql, {"id": id})
        self.con.commit()     


if __name__ == "__main__":
    data = MyData()
    data.add("测试", 1, datetime.now())
    d = data.get(2)
    data.update(2, "ttt", 10, datetime.now())
    data.delete(3)
    print(d)
